package ch.escaflowne.mangalist;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

public class Manga extends SQLiteOpenHelper
{
	Context _context;
	private static final String DATENBANK_NAME = "manga.db";
	private static final int DATENBANK_VERSION = 2;
	
	Manga(Context context)
	{
		super (context, DATENBANK_NAME, null, DATENBANK_VERSION);

		_context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		// Create SQL database to store manga information
		db.execSQL("CREATE TABLE manga(_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, autor TEXT, volumes INTEGER)");
		// Separate volumes in a second table
		//db.execSQL("CREATE TABLE volumes(isbn TEXT PRIMARY KEY, _id INTEGER, volume INTEGER)");	//V1
		db.execSQL("CREATE TABLE volumes(_id INTEGER PRIMARY KEY, mangaid INTEGER, volume INTEGER)");
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		//deleteAll();
		
		db.execSQL("ALTER TABLE manga RENAME TO mangabak");
		db.execSQL("ALTER TABLE volumes RENAME TO volumesbak");
		onCreate(db);
		db.execSQL("INSERT INTO manga(_id,title,autor,volumes) SELECT _id,title,autor,volumes FROM mangabak");
		db.execSQL("INSERT INTO volumes(_id,mangaid,volume) SELECT isbn,_id,volume FROM volumesbak");
		
		db.execSQL("DROP TABLE mangabak");
		db.execSQL("DROP TABLE volumesbak");
		
	}
	
	public void deleteAll()
	{
		SQLiteDatabase db;
		db = getWritableDatabase();
		
		db.execSQL("DROP TABLE manga");
		db.execSQL("DROP TABLE volumes");
	}
	
	public boolean addManga(String title, String autor, int volumes)
	{
		try
		{
			SQLiteDatabase db = getWritableDatabase();
			db.execSQL("INSERT INTO manga(title,autor,volumes) VALUES ('" + title + "','" + autor + "','" + volumes+"')");
		}
		catch (SQLException e)
		{
			Log.w("SQL", e.toString());
			return false;
		}
		
		return true;
	}
	
	public boolean removeManga(int _id)
	{
		SQLiteDatabase db = getWritableDatabase();
		try
		{
			//Remove manga from list
			db.execSQL("DELETE FROM manga WHERE _id = ?", new String[]{String.valueOf(_id)});
			//Remove all volumes
			db.execSQL("DELETE FROM volumes WHERE mangaid = ?", new String[]{String.valueOf(_id)});
			return true;
		}catch (SQLException e)
		{
			Log.e("SQL", e.toString());
			return false;
		}
		
	}
	
	public boolean addVolume(String isbn, int mangaid, int volume)
	{
		Log.i("SQL", "Adding volume, ISBN: " + isbn + ", mangaid: " + mangaid + ", volume: " + volume);
		try
		{
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("INSERT INTO volumes (_id,mangaid,volume) VALUES('" + isbn + "','" + mangaid + "','" + volume + "')");
		}
		catch (SQLException e)
		{
			Log.e("SQL", e.toString());
			return false;
		}
		
		return true;
	}
	
	public void removeVolume(String isbn)
	{
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("DELETE FROM volumes WHERE _id = ?", new String[]{isbn});
	}
	
	public int findMangabyISBN(String isbn)
	{
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor;
		try
		{
			cursor = db.rawQuery("SELECT mangaid FROM volumes WHERE _id = " + isbn, null);
		
			// return primary key (_id) from volumes, or -1 if isbn was not found 
			if (cursor.moveToFirst())
				return cursor.getInt(0);
		}
		catch (SQLException e)
		{
			Log.e("SQL", e.toString());
		}
		return -1;
	}
	
	public int findMangabyTitle(String title)
	{
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor;
		
		cursor = db.rawQuery("SELECT _id FROM manga WHERE title = ?", new String[]{title});
		if (cursor.moveToFirst())
		return cursor.getInt(0);
		
		return -1;
	}
	
	public String getTitlebyID(int _id)
	{
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor;
		
		cursor = db.rawQuery("SELECT title FROM manga WHERE _id = ?", new String[]{String.valueOf(_id)});
		
		if (cursor.moveToFirst())
			return cursor.getString(0);
		
		return null; 
	}
	
	public static final int GET_VOLUMES_ISBN = 0;
	public static final int GET_VOLUMES_VOLUME = 1;
	public Cursor getVolumes(int mangaid)
	{
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor;
		
		cursor = db.rawQuery("SELECT _id,volume FROM volumes WHERE mangaid = ? ORDER BY volume", new String[]{String.valueOf(mangaid)});
		return cursor;
	}
	
	public static final int GET_MANGA_ID = 0;
	public static final int GET_MANGA_TITLE = 1;
	public static final int GET_MANGA_AUTOR = 2;
	public static final int GET_MANGA_VOLUMES = 3;
	public Cursor getMangaCursor()
	{
		SQLiteDatabase db = getReadableDatabase();
		
		return db.rawQuery("SELECT _id,title,autor,volumes FROM manga ORDER BY title", null);
	}
	
	public boolean editManga(int _id, String title, String autor, int volumes)
	{
		SQLiteDatabase db = getWritableDatabase();
		
		try{
			db.execSQL("UPDATE manga SET title='" + title + "' WHERE _id='" + String.valueOf(_id) + "'");
			db.execSQL("UPDATE manga SET autor='" + autor + "' WHERE _id='" + String.valueOf(_id) + "'");
			db.execSQL("UPDATE manga SET volumes='" + volumes + "' WHERE _id='" + String.valueOf(_id) + "'");
			return true;
		}catch(SQLException e)
		{
			Log.e("MangaListe", e.toString());
			return false;
		}
	}
	
	public int getVolumeCount(int mangaid)
	{
		try{
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery("SELECT COUNT(*) FROM volumes WHERE mangaid=?", new String[]{String.valueOf(mangaid)});
		if (c.moveToFirst())
			return c.getInt(0);
		}catch(SQLException e)
		{
			Log.e("MangaListe", "Error in Manga.getVolumeCount( " + mangaid + " )");
			Log.e("MangaListe", e.toString());
		}
		return 1;
		
	}
	
	public int getTotalVolumeCount()
	{
		try{
			SQLiteDatabase db = getReadableDatabase();
			Cursor c = db.rawQuery("SELECT COUNT(*) FROM volumes", null);
			if (c.moveToFirst())
				return c.getInt(0);
			}catch(SQLException e)
			{
				Log.e("MangaListe SQL", "Error in Manga.getTotalVolumeCount()");
				Log.e("MangaListe SQL", e.toString());
			}
			return -1;
	}
	
	public int getTotalMangaCount()
	{
		try{
			SQLiteDatabase db = getReadableDatabase();
			Cursor c = db.rawQuery("SELECT COUNT(*) FROM manga", null);
			if (c.moveToFirst())
				return c.getInt(0);
			}catch(SQLException e)
			{
				Log.e("MangaListe SQL", "Error in Manga.getTotalMangaCount()");
				Log.e("MangaListe SQL", e.toString());
			}
			return -1;
	}
	
	
}
